/*
 * DbJsonConnection.cpp
 *
 *  Created on: 2018年3月4日
 *      Author: zhengboyuan
 */

#include "DbJsonConnection.h"
#include "TStringUtil.h"
#include "JsonUtil.h"
#include "CLog.h"


template <>
bool fromRow(Json::Value& json, db::DbRow& row)
{
    bool done = false;
    try
    {
        for (size_t i = 0; i < row.size(); ++i)
        {
            db::DataType dataType = row.getType(i);
            std::string name = row.getName(i);

            if (dataType == db::DT_NULL)
            {
                json[name] = Json::nullValue;
            }
            else if (dataType == db::DT_STRING)
            {
                std::string value;
                row.getValue(i, value);
                json[name] = value;
            }
            else if (dataType == db::DT_INTEGER)
            {
                int value = 0;
                row.getValue(i, value);
                json[name] = value;
            }
            else if (dataType == db::DT_LONG_LONG)
            {
                int64_t value = 0;
                row.getValue(i, value);
                json[name] = (Json::Int64)value;
            }
            else if (dataType == db::DT_UNSIGNED_LONG_LONG)
            {
                int64_t value = 0;
                row.getValue(i, value);
                json[name] = (Json::Int64)value;
            }
            else if (dataType == db::DT_DOUBLE)
            {
                double value = 0;
                row.getValue(i, value);
                json[name] = value;
            }
            else if (dataType == db::DT_DATE)
            {
                time_t t = 0;
                row.getTime(i, t);
                json[name] = t;
            }
        }

        done = true;
    }
    catch (std::exception& ex)
    {
        CLog::warning("toJson ex. %s\n", ex.what());
    }
    return done;
}



namespace db
{



DbJsonConnection::DbJsonConnection(DbConnectionPool& pool):
		m_pool(pool)
{
}

DbJsonConnection::~DbJsonConnection()
{
}

bool DbJsonConnection::validate()
{
	db::DbConnection conn(m_pool);
	return conn.validate();
}

bool DbJsonConnection::query(const std::string& sql, Json::Value& json)
{
	db::DbConnection conn(m_pool);
	if (!conn.validate())
	{
		return false;
	}

    return conn.queryObject(sql, json);
}

bool DbJsonConnection::query(const std::string& sql, JsonList& jsonList)
{
	db::DbConnection conn(m_pool);
	if (!conn.validate())
	{
		return false;
	}

    conn.queryObjects(sql, jsonList);
    return true;
}

bool DbJsonConnection::rowToJson(db::DbRow& row, Json::Value& json)
{
    return fromRow(json, row);
}

std::string DbJsonConnection::makeUpdate(const std::string& table, const Json::Value& json)
{
	std::ostringstream ss;
	ss << "UPDATE ";
	ss << table;
	ss << " set ";
	ss << Json::join(json, "=", ",", '`', "", "", Json::kIgnoreNull | Json::kQuoteString);
	return ss.str();
}

std::string DbJsonConnection::makeInsert(const std::string& table, const std::string& verb, const Json::Value& json)
{
	std::ostringstream ss;
	ss << verb;
	ss << " ";
	ss << table;
	ss << " ";
	ss << Json::joinKey(json, ",", '`', "(", ")", Json::kIgnoreNull | Json::kQuoteString);
	ss << " ";
	ss << Json::joinValue(json, ",", "values(", ")", Json::kIgnoreNull | Json::kQuoteString);
	return ss.str();
}

std::string DbJsonConnection::toJsonString(int offset, int limit, int total, JsonList& items)
{
	Json::Value json;
	json["offset"] = offset;
	json["limit"] = limit;
	json["total"] = total;

	Json::Value subjson(Json::arrayValue);
	for (size_t i = 0; i < items.size(); i ++)
	{
		subjson.append(items[i]);
	}

	json["items"] = subjson;
	return json.toStyledString();
}

bool DbJsonConnection::queryToJson(const std::string& sql, std::string& text)
{
	Json::Value json;
	if (!query(sql, json))
	{
		return false;
	}

	text = json.toStyledString();
	return true;
}

bool DbJsonConnection::querySomeToJson(const std::string& sql, std::string& text)
{
	JsonList jsonList;
	if (!query(sql, jsonList))
	{
		return false;
	}

	Json::Value json;
	listToJson(jsonList, json);

	text = json.toStyledString();

	return true;
}

bool DbJsonConnection::execute(const std::string& sql)
{
	db::DbConnection conn(m_pool);
	if (!conn.validate())
	{
		return false;
	}

	return conn.execute(sql);
}

int DbJsonConnection::update(const std::string& sql)
{
	db::DbConnection conn(m_pool);
	if (!conn.validate())
	{
		return false;
	}

	return conn.update(sql);
}

bool DbJsonConnection::update(const std::string& table, const Json::Value& json, const std::string& condition)
{
	std::string sql = makeUpdate(table, json);
	sql += " ";
	sql += condition;
	return update(sql) > 0;
}

bool DbJsonConnection::insert(const std::string& sql, const std::string& table, int64_t& id)
{
	db::DbConnection conn(m_pool);
	if (!conn.validate())
	{
		return false;
	}

	return conn.insert(sql.c_str(), table.c_str(), id);
}

bool DbJsonConnection::insert(const std::string& table, const Json::Value& json)
{
	std::string sql = makeInsert(table, "INSERT INTO", json);
	return execute(sql);
}

bool DbJsonConnection::insert(const std::string& table, const Json::Value& json, int64_t& id)
{
	std::string sql = makeInsert(table, "INSERT INTO", json);
	db::DbConnection conn(m_pool);
	if (!conn.validate())
	{
		return false;
	}

	return conn.insert(sql.c_str(), table.c_str(), id);
}

bool DbJsonConnection::replace(const std::string& table, const Json::Value& json)
{
	std::string sql = makeInsert(table, "REPLACE INTO", json);
	return execute(sql);
}


static bool startsWithWhere(const std::string& str)
{
    size_t idx = str.find("where");
    return (idx <= 1);
}


bool DbJsonConnection::searchTable(const std::string& table, const std::string& query,
		const std::string& condition, const std::string& order,
		int offset, int limit, int& total, JsonList& jsonList)
{
	db::DbConnection conn(m_pool);
	if (!conn.validate())
	{
		return false;
	}

	std::string countSql = comn::StringUtil::format("select count(*) from %s", table.c_str());
	if (condition.size())
	{
		countSql += ' ';

		if (!startsWithWhere(condition))
		{
			countSql += "where ";
		}

		countSql += condition;
	}

	if (!conn.queryValue(countSql, total))
	{
		return false;
	}

	std::string sql = query;

	if (condition.size())
	{
		sql += ' ';

        if (!startsWithWhere(condition))
		{
			sql += "where ";
		}

		sql += condition;
	}

	if (order.size() > 0)
	{
		sql += ' ';

		if (!comn::StringUtil::startsWith(order, "order"))
		{
			sql += "order by ";
		}

		sql += order;
	}

	sql += comn::StringUtil::format(" limit %d,%d ", offset, limit);

    conn.queryObjects(sql, jsonList);
    return true;
}


bool DbJsonConnection::search(const std::string& countSql, const std::string& query, 
    const std::string& condition, const std::string& order,
    int offset, int limit, int& total, JsonList& jsonList)
{
    db::DbConnection conn(m_pool);
    if (!conn.validate())
    {
        return false;
    }

    std::string sql = countSql;
    if (condition.size())
    {
        sql += ' ';

        if (!startsWithWhere(condition))
        {
            sql += "where ";
        }

        sql += condition;
    }

    if (!conn.queryValue(sql, total))
    {
        return false;
    }

    sql = query;

    if (condition.size())
    {
        sql += ' ';

        if (!startsWithWhere(condition))
		{
			sql += "where ";
		}

		sql += condition;
	}

	if (order.size() > 0)
	{
		if (comn::StringUtil::startsWith(order, "group by"))
		{
			sql += ' ';
			sql += order;
		}
		else
		{
			sql += ' ';

			if (!comn::StringUtil::startsWith(order, "order"))
			{
				sql += "order by ";
			}

			sql += order;
		}
	}

	sql += comn::StringUtil::format(" limit %d,%d ", offset, limit);

    conn.queryObjects(sql, jsonList);
    return true;
}

bool DbJsonConnection::count(const std::string& countSql, const std::string& condition, int& total)
{
	db::DbConnection conn(m_pool);
	if (!conn.validate())
	{
		return false;
	}

	return count(conn, countSql, condition, total);
}

bool DbJsonConnection::pageSearch(const std::string& table, const std::string& idColumn, const std::string& query,
	const std::string& condition, const std::string& order,
	int offset, int limit, JsonList& jsonList)
{
	db::DbConnection conn(m_pool);
	if (!conn.validate())
	{
		return false;
	}

	return pageSearch(conn, table, idColumn, query, condition, order, offset, limit, jsonList);
}

bool DbJsonConnection::pageSearch(const std::string& table, const std::string& idColumn, const std::string& countSql, const std::string& query,
	const std::string& condition, const std::string& order,
	int offset, int limit, int& total, JsonList& jsonList)
{
	db::DbConnection conn(m_pool);
	if (!conn.validate())
	{
		return false;
	}
	
	if (!count(conn, countSql, condition, total))
	{
		return false;
	}

	return pageSearch(conn, table, idColumn, query, condition, order, offset, limit, jsonList);
}

void DbJsonConnection::groupJson(const JsonList& src, const std::string& field, JsonListGroup& jsonGroup)
{
    //todo
}

bool DbJsonConnection::listToJson(const JsonList& jsonList, Json::Value& json)
{
    json = Json::Value(Json::arrayValue);
    for (const Json::Value& item : jsonList)
    {
        json.append(item);
    }
    return true;
}

bool DbJsonConnection::count(db::DbConnection& conn, const std::string& countSql, const std::string& condition, int& total)
{
	std::string sql = countSql;
	if (condition.size())
	{
		sql += ' ';

		if (!startsWithWhere(condition))
		{
			sql += "where ";
		}

		sql += condition;
	}

	return conn.queryValue(sql, total);
}

bool DbJsonConnection::pageSearch(db::DbConnection& conn, const std::string& table, const std::string& idColumn, const std::string& query,
	const std::string& condition, const std::string& order,
	int offset, int limit, JsonList& jsonList)
{
	std::string sql = query;

	std::string subWhere;
	if (condition.size())
	{
		if (!startsWithWhere(condition))
		{
			subWhere += "where ";
		}

		subWhere += condition;
	}

	std::string whereLine = comn::StringUtil::format(" WHERE %s.%s IN (SELECT * FROM (SELECT %s FROM %s %s %s limit %d,%d) as tmp) %s",
		table.c_str(), idColumn.c_str(), idColumn.c_str(), table.c_str(),
		subWhere.c_str(), order.c_str(), offset, limit,
		order.c_str());

	sql += whereLine;

	conn.queryObjects(sql, jsonList);
	return true;
}
bool DbJsonConnection::querySome(const std::string& sql, Json::Value& json)
{
	bool got = false;
	JsonList jsonList;
	if (query(sql, jsonList))
	{
		toJson(jsonList, json);
		got = true;
	}
	else
	{
		json = Json::Value(Json::arrayValue);
	}
	return got;
}

bool DbJsonConnection::toJson(const JsonList& jsonList, Json::Value& json)
{
    json = Json::Value(Json::arrayValue);
    for (const Json::Value& item : jsonList)
    {
        json.append(item);
    }
    return true;
}

bool DbJsonConnection::toJson(int offset, int limit, int total, const JsonList& items, Json::Value& json)
{
	json = Json::Value(Json::objectValue);
	json["offset"] = offset;
	json["limit"] = limit;
	json["total"] = total;

	Json::Value subjson(Json::arrayValue);
	for (size_t i = 0; i < items.size(); i++)
	{
		subjson.append(items[i]);
	}

	json["items"] = subjson;
	
	return true;
}


} /* namespace db */
